// MySQL群组表结构定义
const groupTablesConfig = {
  groups: `
    CREATE TABLE IF NOT EXISTS \`groups\` (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      description TEXT,
      creator_id VARCHAR(255) NOT NULL,
      group_avatar VARCHAR(255),
      group_code VARCHAR(32) NOT NULL UNIQUE,
      status ENUM('active', 'disbanded') NOT NULL DEFAULT 'active',
      disbanded_at TIMESTAMP NULL,
      disbanded_by VARCHAR(255) NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX (creator_id),
      INDEX (group_code),
      INDEX (status)
    )
  `,

  group_members: `
    CREATE TABLE IF NOT EXISTS group_members (
      group_id INT NOT NULL,
      user_id VARCHAR(255) NOT NULL,
      role ENUM('member', 'admin') NOT NULL DEFAULT 'member',
      joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (group_id, user_id),
      INDEX (user_id)
    )
  `,

  group_invitations: `
    CREATE TABLE IF NOT EXISTS group_invitations (
      id INT AUTO_INCREMENT PRIMARY KEY,
      group_id INT NOT NULL,
      from_user_id VARCHAR(255) NOT NULL,
      to_user_id VARCHAR(255) NOT NULL,
      status ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX (group_id),
      INDEX (from_user_id),
      INDEX (to_user_id),
      UNIQUE KEY unique_invitation (group_id, from_user_id, to_user_id, status)
    )
  `,

  group_join_requests: `
    CREATE TABLE IF NOT EXISTS group_join_requests (
      id INT AUTO_INCREMENT PRIMARY KEY,
      group_id INT NOT NULL,
      user_id VARCHAR(255) NOT NULL,
      status ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      INDEX (group_id),
      INDEX (user_id),
      UNIQUE KEY unique_request (group_id, user_id, status)
    )
  `
};

module.exports = groupTablesConfig;